﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.IO;
using System.Windows.Forms;
using TestReport.Generator.Objects;

namespace TestReport.Generator
{
    public class QueryManager
    {
        private const string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0 Xml;HDR=YES;'";
        private const string pathCD = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\HP\Desktop\Proyecto Freelance\viewCicliDettaglio.csv;Extended Properties='Excel 12.0;HDR=Yes;'";
        private const string pathAL = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\HP\Desktop\Proyecto Freelance\viewAltreLavorazioniReport.csv;Extended Properties='Excel 8.0;HDR=Yes;'";

        public static List<CA> GetAllCAs()
        {
            List<CA> CAs = new List<CA>();
            string connectionStringAs = string.Empty;

            OpenFileDialog openfileDialog1 = new OpenFileDialog() { Title = "cicliAvarie", FileName = "cicliAvarie.xls", Filter = "Excel Files|*.xls;*.xlsx;*.xlsm" };
            if (openfileDialog1.ShowDialog() != System.Windows.Forms.DialogResult.OK)
                return null;

            connectionStringAs = string.Format(connectionString, openfileDialog1.FileName);

            using (OleDbConnection connection = new OleDbConnection(connectionStringAs))
            {
                connection.Open();
                OleDbCommand command = new OleDbCommand("select * from [cicliAvarie$]", connection);
                using (OleDbDataReader dr = command.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        CA ca = new CA();
                        ca.ID = Convert.ToInt32(dr[0]);
                        ca.CIC_ID = Convert.ToInt32(dr[1]);
                        ca.DESC = Convert.ToString(dr[2]);

                        CAs.Add(ca);
                    }
                }
                connection.Close();
            }

            return CAs;
        }

        public static List<CD> GetAllCDs()
        {
            List<CD> CDs = new List<CD>();
            string connectionStringCD = string.Empty;

            OpenFileDialog openfileDialog1 = new OpenFileDialog() { Title = "viewCicliDettaglio", FileName = "viewCicliDettaglio.xls", Filter = "Excel Files|*.xls;*.xlsx;*.xlsm" };
            if (openfileDialog1.ShowDialog() != System.Windows.Forms.DialogResult.OK)
                return null;

            connectionStringCD = string.Format(connectionString, openfileDialog1.FileName);


            using (OleDbConnection connection = new OleDbConnection(connectionStringCD))
            {
                connection.Open();
                OleDbCommand command = new OleDbCommand("select * from [viewCicliDettaglio$]", connection);
                using (OleDbDataReader dr = command.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        CD cd = new CD();
                        cd.ID = Convert.ToInt32(dr[0]);
                        cd.CIC_ID = Convert.ToInt32(dr[1]);
                        cd.ML_DESCR = Convert.ToString(dr[2]);
                        cd.QTA = Convert.ToInt32(dr[3]);
                        cd.RIPR = Convert.ToInt32(dr[4]);
                        cd.SOST = Convert.ToInt32(dr[5]);
                        cd.CONT = Convert.ToInt32(dr[6]);
                        cd.NOTE = Convert.ToString(dr[7]);
                        cd.COMP = Convert.ToString(dr[8]);
                        cd.OPE = Convert.ToString(dr[9]);

                        CDs.Add(cd);
                    }
                }
                connection.Close();
            }

            return CDs;
        }

        public static List<AL> GetAllALs()
        {
            List<AL> ALs = new List<AL>();
            string connectionStringAl = string.Empty;

            OpenFileDialog openfileDialog1 = new OpenFileDialog() { Title = "viewAltreLavorazioniReport", FileName = "viewAltreLavorazioniReport.xls", Filter = "Excel Files|*.xls;*.xlsx;*.xlsm" };
            if (openfileDialog1.ShowDialog() != System.Windows.Forms.DialogResult.OK)
                return null;

            connectionStringAl = string.Format(connectionString, openfileDialog1.FileName);


            using (OleDbConnection connection = new OleDbConnection(connectionStringAl))
            {
                connection.Open();
                OleDbCommand command = new OleDbCommand("select * from [viewAltreLavorazioniReport$]", connection);
                using (OleDbDataReader dr = command.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        AL al = new AL();
                        al.ID = Convert.ToInt32(dr[0]);
                        al.CIC_ID = Convert.ToInt32(dr[1]);
                        al.LAVORO = Convert.ToString(dr[2]);
                        al.QTA = Convert.ToInt32(dr[3]);
                        al.RIPARAZIONE = Convert.ToBoolean(dr[4] == "X" ? true : false);
                        al.SOSTITUZIONE = Convert.ToBoolean(dr[5] == "X" ? true : false);
                        al.CONTROLLO = Convert.ToBoolean(dr[6] == "X" ? true : false);
                        al.NOTE = Convert.ToString(dr[7]);
                        al.OPE = Convert.ToString(dr[8]);
                        al.COMPLETATO = Convert.ToBoolean(dr[9] == "X" ? true : false);

                        ALs.Add(al);
                    }
                }
                connection.Close();
            }

            return ALs;
        }
    }
}

